<?php
class Menu {
    function __construct() {}
    
    /**
     * 
     * @param type $location frontend or backend
     * @return type
     */
    public static function menu($location){
        $menu = array();
        
        $connection     = Yii::app()->db;
        
        //Queries
        $sql            = "SELECT `id`, `label`, `link`, `parent` FROM menu WHERE parent = 0 AND location = '$location';";        
        $sqlSub         = "SELECT `id`, `label`, `link`, `parent` FROM menu WHERE parent != 0 ORDER BY parent;";
        $menuCommand    = $connection->createCommand($sql);
        $menuCommandSub = $connection->createCommand($sqlSub);
        $rows           = $menuCommand->queryAll();
        $rowsSub        = $menuCommandSub->queryAll();
        
        //Creo il menu
        foreach ($rows as $keyMenu => $menuValue){
            //Voce principale del menu
            $menu['items'][$keyMenu]['label'] = $menuValue['label'];
            $menu['items'][$keyMenu]['url']   = array($menuValue['link']);
            //Sub menu
            $cont = 0;//Contatore voce sub menu, indica la posizione nell'array
            foreach ($rowsSub as $keySub => $valueSub){
                if($menuValue['id'] == $valueSub['parent']){
                    $cont ++;
                    
                    $menu['items'][$keyMenu]['items'][$cont]['label']    = $valueSub['label'];
                    $menu['items'][$keyMenu]['items'][$cont]['url']      = array($valueSub['link']);
                }
            }
        }
        
        return $menu;
    }
}